<?php
	
	function rand_color(){  
    for($a=0;$a<6;$a++){    //采用#FFFFFF方法，  
        $d.=dechex(rand(0,15));//累加随机的数据--dechex()将十进制改为十六进制  
    }  
    return '#'.$d;  
}  


class Mysql{
    private $host;
    private $name;
    private $pass;
    private $table;
    private $ut;
    function __construct($host,$name,$pass,$table,$ut){
		$this->host=$host;
		$this->name=$name;
		$this->pass=$pass;
		$this->table=$table;
		$this->ut=$ut;
		$this->connect();
    }

    function connect(){
		$link=mysql_connect($this->host,$this->name,$this->pass) or die ($this->error());
		mysql_select_db($this->table,$link) or die("没该数据库：".$this->table);
		mysql_query("SET NAMES '$this->ut'");
    }

    function query($sql, $category = '') {
		if(!($query = mysql_query($sql))) $this->show('Say:', $sql);
		return $query;
    }

    function show($message = '', $sql = '') {
    	if(!$sql) echo $message;
    	else echo $message.'<br>'.$sql;
    }
	
	function fetch_array($query) {
		return mysql_fetch_array($query);
	}
	
	function num_rows($query) {
		return mysql_num_rows($query);
	}

	function company($company_id){
		$sql = "select name from companys where company_id=$company_id";
		$query =$this->query($sql);
		$result = $this->fetch_array($query);
		return $result[0];
	}
	
	function select_company_countByyear($time1, $time2){
		$where=" where 1=1  ";
		$where.=" and  project.start_time>=".$time1." and project.start_time<".$time2;
		
		$sql = "select count(*) as count  from  project inner join student on student.student_id=project.student_id inner join companys on student.company_id = companys.company_id ".$where ;
		$query = $this->query($sql);
   	   while($row=$this->fetch_array($query)){
			$result[] = array('count'=>$row['count'],'company_id'=>$row['company_id']);
		}
		return $result;
	}
	
	function selecttopcompany(){
		$where=" where 1=1  ";

		$sql= " SELECT count( * ) AS count, company_id FROM `student` $where GROUP BY company_id ORDER BY count DESC LIMIT 10 ";
		$query = $this->query($sql);
		while($row=$this->fetch_array($query)){
			$result[] = array('count'=>$row['count'],'company_id'=>$row['company_id'],'company_name'=>$this->company($row['company_id']));
		}
		return $result;
	}


	function updateclassteacher($array){
		$sql="update class_teacher set category='0' where id in (".implode(',',$array).")";
		$query = $this->query($sql);
		return $query;
	}
	
    function select_projects($start,$end,$teacher_id,$student_id,$search_name,$search_key_words,$search_summary,$classes){
		$where = " where 1=1 ";
		if($search_name!=""){
			$where.= " and title like '%$search_name%'";
		} 
		if($search_key_words!=""){
			$where.=" and key_word like '%$search_key_words%'";
		}
		if($search_summary!=""){
			$where.=" and summary like '%$search_summary%'";
		}
		if(count($classes)!=0){
			$where.= "and (";
			for($i=0;$i<count($classes);$i++){
				$where.=" class_id=".$classes[$i]['class_id'];
				if($i!=count($classes)-1){
					$where.=" or ";
				}
			}
			$where.= " )";
		}
        if($end!=""){
              $where.=" order by create_time desc limit $start,$end";
        }

        //$sql = "SELECT `project`.*, `teacher`.`name` AS `teacher_name`, `student`.`name` AS `student_name` FROM `project` INNER JOIN `teacher` ON teacher.teacher_id = project.teacher_id INNER JOIN `student` ON student.student_id=project.student_id $where ";
		$sql = "SELECT * FROM `project` $where";
        $query = $this->query($sql);
        $count = $this->num_rows($query);
		if($count=="0"){
			return $count;
		}else{
			while($row=$this->fetch_array($query)){
				$result[]=$row;
			}
			return $result;
		}
    }
    
	//teacherlist
	function select_teachers($start,$end,$search_name){
		$where = " where 1=1";
		if($search_name!=""){
			$where.=" and name like '%$search_name%'";
		}

		if($end!=""){
			$where.=" order by teacher_id desc limit $start,$end";
		}
		$sql = "SELECT * FROM teacher $where ";
		$query = $this->query($sql);
		$count = $this->num_rows($query);
		if($count=="0"){
			return $count;
		}else{
			while($row=$this->fetch_array($query)){
				$result[]=$row;
			}
			return $result;
		}
	}
	
	//Classlist
	function select_classes($start,$end,$teacher_id,$search_name,$search_start_time,$search_location){
		if($teacher_id!=""){
			$where.= " and teacher_id=$teacher_id";
		}
		if($search_name!=""){
			$where.=" and class.name like '%$search_name%'";
		} 
		if($search_start_time!=""){
			$where.=" and start_time='$search_start_time'";
		}
		if($search_location!=""){
			$where.=" and location like '$search_location'";
		}
		if($end!=""){
			$where.=" order by class.class_id desc limit $start,$end";
		}
		$sql = "select class.*,teacher.name as teacher_name from class, teacher,class_teacher  where class.class_id=class_teacher.class_id and teacher.teacher_id=class_teacher.teacher_id  $where ";
		$query = $this->query($sql);
		$count = $this->num_rows($query);
		if($count=="0"){
			return $count;
		}else{
		while($row=$this->fetch_array($query)){
			$result[]=$row;
			}
			return $result;
		}
	}
	
	//课程列表
	function select_courses($start,$end,$search_name){
		$where = "where 1=1";
		if($search_name!=""){
			$where.=" and name like '%$search_name%'";
		} 
		if($end!=""){
			$where.=" order by course_id desc limit $start,$end";
		}
        $sql = "SELECT * FROM `course` $where";
        $query = $this->query($sql);
        $count = $this->num_rows($query);
		if($count=="0"){
			return $count;
		}else{
			while($row=$this->fetch_array($query)){
				$result[]=$row;
			}
            return $result;
		}
	}

	function selectstudentforteacher($teacher_id){
		$sql = "SELECT `student`.*, `class`.*  FROM `student` INNER JOIN `class` ON class.class_id = student.class_id where class.teacher_id=$teacher_id ";
		$query = $this->query($sql);
		$count = $this->num_rows($query);
		if($count=="0"){
			return $count;
		}else{
			while($row=$this->fetch_array($query)){
				$result[]=$row;
			}
			return $result;
		}
	}

	function selectstudentprojectby_companyid($company_id){
		$sql = "SELECT `student`.*, `project`.`title` AS `project_name`,`project`.`category`  FROM `student` INNER JOIN `project` ON student.student_id = project.student_id where student.company_id=$company_id order by project.category desc ";
		$query = $this->query($sql);
		$count = $this->num_rows($query);
		if($count=="0"){
			return $count;
		}else{
			while($row=$this->fetch_array($query)){
			$result[]=$row;
		}
		return $result;
		}
	}

	function selectstudentprojectinarray($array,$start,$end){
		if(count($array)==0){ $array =array(0);}
		$sql = "SELECT `project`.*, `student`.`name` AS `student_name`  FROM `student` INNER JOIN `project` ON student.student_id = project.student_id where student.student_id in (".implode(',',$array).") order by student_id desc ";
		if($start!=9999){
			$sql.="  limit $start,$end";
		}
		$query = $this->query($sql);
		$count = $this->num_rows($query);
		if($count=="0"){
			return $count;
		}else{
			while($row=$this->fetch_array($query)){
				$result[]=$row;
			}
			return $result;
		}
	}

	//查询comment表和user表
    function select_comment($start,$end,$cid,$category){
        $where = "where comment.cid=$cid and comment.category=$category";
        if($end!=""){
			$where.=" order by id desc limit $start,$end";
        }
        $sql = "SELECT `comment`.*, `user`.`user_name` AS `teacher_name`  FROM `comment` INNER JOIN `user` ON comment.user_id = user.user_id $where ";
        $query = $this->query($sql);
		$count = $this->num_rows($query);
		if($count=="0"){
			return $count;
		}else{
			while($row=$this->fetch_array($query)){
				$result[]=$row;
			}
			return $result;
       }
	}

	function select_classteacher($class_id){
		if($class_id != ""){
			$where = "where class_teacher.class_id=$class_id";
		}else{
			$where.= " where 1=1";
		}
        $sql = "SELECT `teacher`.*, `class_teacher`.`category` AS `classteacher_type`,`class_teacher`.`id` AS `classteacher_id` FROM `class_teacher` INNER JOIN `teacher` ON teacher.teacher_id = class_teacher.teacher_id  $where ";
        $query = $this->query($sql);
		$count = $this->num_rows($query);
		if($count=="0"){
			return $count;
		}else{
			while($row=$this->fetch_array($query)){
				$result[]=$row;
			}
			return $result;
		}
	}


	function select_classstudent($class_id){
		if($class_id!=""){
			$where = "where student_class.class_id=$class_id";
		}else{
			$where.=" where 1=1";
		}
		$sql = "SELECT `student`.*, `student_class`.* FROM `student_class` INNER JOIN `student` ON student_class.student_id = student.student_id  $where ";
		$query = $this->query($sql);
		$count = $this->num_rows($query);
		if($count=="0"){
			return $count;
		}else{
			while($row=$this->fetch_array($query)){
				$result[]=$row;
			}
			return $result;
		}
	}

	//取得所在表的所有字段
	function describe($table){
		$sql = "SELECT COLUMN_NAME,COLUMN_COMMENT FROM information_schema.columns WHERE table_name = '$table'";
		$query = $this->query($sql);
		while($row=$this->fetch_array($query)){
			$result[]=$row;
		}
		return $result;
	}
	
	//取得所在表的所有字段
	function describe_name($table){
		$sql = "SELECT COLUMN_NAME FROM information_schema.columns WHERE table_name = '$table'";
		$query = $this->query($sql);
		while($row=$this->fetch_array($query)){
			$result[]=$row;
		}
		return $result;
	}
	
	//删除表格字段
	function delete_field($table,$field){
		$sql = "ALTER TABLE `$table` DROP `$field`";
		$query = $this->query($sql);
		return $query;
	}
  
	//更新表格字段
	function edit_field($table,$before,$after,$comment){
		$sql = "ALTER TABLE `$table` CHANGE `$before` `$after` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '$comment'";
		$query = $this->query($sql);
		return $query;
	}

	//模板表格增加字段
	function add_field($table,$value,$comment){
		$sql = "ALTER TABLE `$table` ADD `$value` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '$comment'";
		$query = $this->query($sql);
		return $query;
	}
	
	//导入的插入
	function insert($table,$data){
		$str="";
		$ziduan="";
		$count = count($data);
		$j=0;
		//取得所有的值
		foreach ($data as $k=>$value){
			$j++;
			$str.="'".$value."'";
			$ziduan.=$k;
			if($j!=$count){
				$ziduan.=",";
				$str.=",";
			}
		}
		$sql = "INSERT INTO $table ($ziduan)values($str)";
		$result = mysql_query($sql);
		if($result){
			return $result;
		}else{
			return $sql;
		}
     }

	//标签树查询
	function gettagtree($tree_id, $pid=0){
		$sql = "SELECT `tag`.*, `tag_tree`.*  FROM `tag_tree` INNER JOIN `tag` ON tag.tag_id = tag_tree.tag_id  where tag_tree.tree_id='$tree_id' AND tag_tree.pid='$pid'";
		//return $sql;
        $query = $this->query($sql);
		while($row=$this->fetch_array($query)){
			$data[] = array('id'=>$row['id'], 'tag_id'=>$row['tag_id'], 'name'=>$row['name']);
			$result=$this->gettagtree($tree_id, $row['tag_id']);
			if(!empty($result)){
				$data[]=$result;
			}
        }
		return $data;
	}
	
	function getStudentClassOrderStudentNum(){
		$sql = "SELECT COUNT(*) AS count_num,* FROM `student_class` GROUP BY class_id ORDER BY count_num";
		$query = $this->query($sql);
		while($row=$this->fetch_array($query)){
			$data[] = $row;
        }
		return $data;
	}
	
	
}

?>
